package Dao;

import Bean.*;
import cart.ShoppingCart;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassDao {
    private JdbcTemplate jdbcTemplate;
    String[] span={"","一","二","三","四","五","六","日"};
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    public BeanClass getClassDetails(int id){
        String sql="select class_id,class_name,class_state,class_price,teacher_id,place_id,class_url,num from class where class_id=?";
        BeanClass myclass=new BeanClass();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanClass>() {
            @Override
            public BeanClass extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                if(rs.next()) {
                    myclass.setClass_id(rs.getInt(1));
                    myclass.setClass_name(rs.getString(2));
                    myclass.setClass_state(rs.getBoolean(3));
                    myclass.setClass_price(rs.getDouble(4));
                    myclass.setTeacher_id(rs.getString(5));
                    myclass.setPlace_id(rs.getInt(6));
                    myclass.setClass_url(rs.getString(7));
                    myclass.setNum(rs.getInt(8));
                }
                return null;
            }},id);
        sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher where teacher_id=?";
        BeanTeacher teacher=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
            @Override
            public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                if(rs.next()) {
                    BeanTeacher teacher=new BeanTeacher();
                    teacher.setTeacher_id(rs.getString(1));
                    teacher.setTeacher_name(rs.getString(2));
                    teacher.setTeacher_photo(rs.getString(3));
                    teacher.setTeacher_introduction(rs.getString(4));
                    return teacher;
                }
                return null;
            }},myclass.getTeacher_id());

        sql="select place_id,place_name,place_photo,place_introduction from place where place_id=?";
        BeanPlace place=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
            @Override
            public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                if(rs.next()) {
                    BeanPlace place=new BeanPlace();
                    place.setPlace_id(rs.getInt(1));
                    place.setPlace_name(rs.getString(2));
                    place.setPlace_photo(rs.getString(3));
                    place.setPlace_introduction(rs.getString(4));
                    return place;
                }
                return null;
            }},myclass.getPlace_id());

        int num=this.jdbcTemplate.queryForObject("select count(*) from order_details where class_id=?", Integer.class,myclass.getClass_id());
        myclass.setNum(num);

        List<BeanCourse> courses = new ArrayList<BeanCourse>();
        sql="select course_id,start_time,finish_time,course_state,course_evaluation,weekday from course where class_id=?";
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanCourse>() {
            @Override
            public BeanCourse extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanCourse course=new BeanCourse();
                    course.setCourse_id(rs.getInt(1));
                    course.setStart_time(rs.getDate(2));
                    course.setFinish_time(rs.getDate(3));
                    course.setCourse_state(rs.getBoolean(4));
                    course.setCourse_evaluation(rs.getString(5));
                    course.setWeekday(rs.getInt(6));
                    courses.add(course);
                }
                return null;
            }},myclass.getClass_id());
        myclass.setCourses(courses);
        myclass.setPlace(place);
        myclass.setTeacher(teacher);
        return myclass;
    }
    public ArrayList<BeanClass> getClassesByTeacher(String id) throws Exception {
        String sql="select class_id,class_name,class_state,class_price,teacher_id,place_id,class_url,num from class where status=1 and teacher_id=?";
        ArrayList<BeanClass> classes=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
            @Override
            public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanClass myclass=new BeanClass();
                    myclass.setClass_id(rs.getInt(1));
                    myclass.setClass_name(rs.getString(2));
                    myclass.setClass_state(rs.getBoolean(3));
                    myclass.setClass_price(rs.getDouble(4));
                    myclass.setTeacher_id(rs.getString(5));
                    myclass.setPlace_id(rs.getInt(6));
                    myclass.setClass_url(rs.getString(7));
                    myclass.setNum(rs.getInt(8));
                    classes.add(myclass);

                }
                return null;
            }},id);
        for(BeanClass myclass:classes){
            sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher where teacher_id=?";
            BeanTeacher teacher=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
                @Override
                public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanTeacher teacher=new BeanTeacher();
                        teacher.setTeacher_id(rs.getString(1));
                        teacher.setTeacher_name(rs.getString(2));
                        teacher.setTeacher_photo(rs.getString(3));
                        teacher.setTeacher_introduction(rs.getString(4));
                        return teacher;
                    }
                    return null;
                }},myclass.getTeacher_id());
            sql="select place_id,place_name,place_photo,place_introduction from place where place_id=?";
            BeanPlace place=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
                @Override
                public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanPlace place=new BeanPlace();
                        place.setPlace_id(rs.getInt(1));
                        place.setPlace_name(rs.getString(2));
                        place.setPlace_photo(rs.getString(3));
                        place.setPlace_introduction(rs.getString(4));
                        return place;
                    }
                    return null;
                }},myclass.getPlace_id());
            List<BeanCourse> courses = new ArrayList<BeanCourse>();
            sql="select course_id,course_state,course_evaluation,weekday,span from course where class_id=?";
            this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanCourse>() {
                @Override
                public BeanCourse extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    while(rs.next()) {
                        BeanCourse course=new BeanCourse();
                        course.setCourse_id(rs.getInt(1));
                        course.setCourse_state(rs.getBoolean(2));
                        course.setCourse_evaluation(rs.getString(3));
                        course.setWeekday(rs.getInt(4));
                        course.setSpan(rs.getInt(5));
                        courses.add(course);
                    }
                    return null;
                }},myclass.getClass_id());
            myclass.setCourses(courses);
            myclass.setPlace(place);
            myclass.setTeacher(teacher);
        }
        return classes;
    }
    public ArrayList<BeanClass> getClassesByPlace(int id,String type) throws Exception {
        String sql="select class_id,class_name,class_state,class_price,teacher_id,place_id,class_url,num from class where status=1 and place_id=?";
        ArrayList<BeanClass> classes=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
            @Override
            public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanClass myclass=new BeanClass();
                    myclass.setClass_id(rs.getInt(1));
                    myclass.setClass_name(rs.getString(2));
                    myclass.setClass_state(rs.getBoolean(3));
                    myclass.setClass_price(rs.getDouble(4));
                    myclass.setTeacher_id(rs.getString(5));
                    myclass.setPlace_id(rs.getInt(6));
                    myclass.setClass_url(rs.getString(7));
                    myclass.setNum(rs.getInt(8));
                    if(myclass.getClass_name().replaceAll("\\d+","").equals(type))
                        classes.add(myclass);
                }
                return null;
            }},id);
        for(BeanClass myclass:classes){
            sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher where teacher_id=?";
            BeanTeacher teacher=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
                @Override
                public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanTeacher teacher=new BeanTeacher();
                        teacher.setTeacher_id(rs.getString(1));
                        teacher.setTeacher_name(rs.getString(2));
                        teacher.setTeacher_photo(rs.getString(3));
                        teacher.setTeacher_introduction(rs.getString(4));
                        return teacher;
                    }
                    return null;
                }},myclass.getTeacher_id());
            sql="select place_id,place_name,place_photo,place_introduction from place where place_id=?";
            BeanPlace place=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
                @Override
                public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanPlace place=new BeanPlace();
                        place.setPlace_id(rs.getInt(1));
                        place.setPlace_name(rs.getString(2));
                        place.setPlace_photo(rs.getString(3));
                        place.setPlace_introduction(rs.getString(4));
                        return place;
                    }
                    return null;
                }},myclass.getPlace_id());
            List<BeanCourse> courses = new ArrayList<BeanCourse>();
            sql="select course_id,course_state,course_evaluation,weekday,span from course where class_id=?";
            this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanCourse>() {
                @Override
                public BeanCourse extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    while(rs.next()) {
                        BeanCourse course=new BeanCourse();
                        course.setCourse_id(rs.getInt(1));
                        course.setCourse_state(rs.getBoolean(2));
                        course.setCourse_evaluation(rs.getString(3));
                        course.setWeekday(rs.getInt(4));
                        course.setSpan(rs.getInt(5));
                        courses.add(course);
                    }
                    return null;
                }},myclass.getClass_id());
            String class_span="周";
            for(BeanCourse course:courses){
                class_span=class_span+span[course.getWeekday()];
            }
            myclass.setClass_span(class_span);
            myclass.setCourses(courses);
            myclass.setPlace(place);
            myclass.setTeacher(teacher);
        }
        return classes;
    }
    public ArrayList<BeanClass> getClassesByType(int type) throws Exception {
        String sql="select class_id,class_name,class_state,class_price,teacher_id,place_id,class_url,num from class where status=1 and type=?";
        ArrayList<BeanClass> classes=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanClass>() {
            @Override
            public BeanClass extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanClass myclass=new BeanClass();
                    myclass.setClass_id(rs.getInt(1));
                    myclass.setClass_name(rs.getString(2));
                    myclass.setClass_state(rs.getBoolean(3));
                    myclass.setClass_price(rs.getDouble(4));
                    myclass.setTeacher_id(rs.getString(5));
                    myclass.setPlace_id(rs.getInt(6));
                    myclass.setClass_url(rs.getString(7));
                    myclass.setNum(rs.getInt(8));
                    classes.add(myclass);
                }
                return null;
            }},type);
        for(BeanClass myclass:classes){
            sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher where teacher_id=?";
            BeanTeacher teacher=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
                @Override
                public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanTeacher teacher=new BeanTeacher();
                        teacher.setTeacher_id(rs.getString(1));
                        teacher.setTeacher_name(rs.getString(2));
                        teacher.setTeacher_photo(rs.getString(3));
                        teacher.setTeacher_introduction(rs.getString(4));
                        return teacher;
                    }
                    return null;
                }},myclass.getTeacher_id());
            sql="select place_id,place_name,place_photo,place_introduction from place where place_id=?";
            BeanPlace place=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
                @Override
                public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanPlace place=new BeanPlace();
                        place.setPlace_id(rs.getInt(1));
                        place.setPlace_name(rs.getString(2));
                        place.setPlace_photo(rs.getString(3));
                        place.setPlace_introduction(rs.getString(4));
                        return place;
                    }
                    return null;
                }},myclass.getPlace_id());
            List<BeanCourse> courses = new ArrayList<BeanCourse>();

            sql="select course_id,course_state,course_evaluation,weekday,span from course where class_id=?";
            this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanCourse>() {
                @Override
                public BeanCourse extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    while(rs.next()) {
                        BeanCourse course=new BeanCourse();
                        course.setCourse_id(rs.getInt(1));
                        course.setCourse_state(rs.getBoolean(2));
                        course.setCourse_evaluation(rs.getString(3));
                        course.setWeekday(rs.getInt(4));
                        course.setSpan(rs.getInt(5));
                        courses.add(course);
                    }
                    return null;
                }},myclass.getClass_id());
            String class_span="周";
            for(BeanCourse course:courses){
                class_span=class_span+span[course.getWeekday()];
            }
            myclass.setClass_span(class_span);
            myclass.setCourses(courses);
            myclass.setPlace(place);
            myclass.setTeacher(teacher);
        }
        return classes;
    }
    public ArrayList<BeanClass> getClassesByKeyword(String keyword) throws Exception {
        String sql="select class_id,class_name,class_state,class_price,teacher_id,place_id,class_url,num from class where status=1 and (class_id like '%"+keyword+"%' or class_name like '%"+keyword+"%') order by `type`";
        ArrayList<BeanClass> classes=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanClass>() {
            @Override
            public BeanClass extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanClass myclass=new BeanClass();
                    myclass.setClass_id(rs.getInt(1));
                    myclass.setClass_name(rs.getString(2));
                    myclass.setClass_state(rs.getBoolean(3));
                    myclass.setClass_price(rs.getDouble(4));
                    myclass.setTeacher_id(rs.getString(5));
                    myclass.setPlace_id(rs.getInt(6));
                    myclass.setClass_url(rs.getString(7));
                    myclass.setNum(rs.getInt(8));
                    classes.add(myclass);
                }
                return null;
            }});
        for(BeanClass myclass:classes){
            sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher where teacher_id=?";
            BeanTeacher teacher=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
                @Override
                public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanTeacher teacher=new BeanTeacher();
                        teacher.setTeacher_id(rs.getString(1));
                        teacher.setTeacher_name(rs.getString(2));
                        teacher.setTeacher_photo(rs.getString(3));
                        teacher.setTeacher_introduction(rs.getString(4));
                        return teacher;
                    }
                    return null;
                }},myclass.getTeacher_id());
            sql="select place_id,place_name,place_photo,place_introduction from place where place_id=?";
            BeanPlace place=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
                @Override
                public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanPlace place=new BeanPlace();
                        place.setPlace_id(rs.getInt(1));
                        place.setPlace_name(rs.getString(2));
                        place.setPlace_photo(rs.getString(3));
                        place.setPlace_introduction(rs.getString(4));
                        return place;
                    }
                    return null;
                }},myclass.getPlace_id());
            List<BeanCourse> courses = new ArrayList<BeanCourse>();

            sql="select course_id,course_state,course_evaluation,weekday,span from course where class_id=?";
            this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanCourse>() {
                @Override
                public BeanCourse extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    while(rs.next()) {
                        BeanCourse course=new BeanCourse();
                        course.setCourse_id(rs.getInt(1));
                        course.setCourse_state(rs.getBoolean(2));
                        course.setCourse_evaluation(rs.getString(3));
                        course.setWeekday(rs.getInt(4));
                        course.setSpan(rs.getInt(5));
                        courses.add(course);
                    }
                    return null;
                }},myclass.getClass_id());
            String class_span="周";
            for(BeanCourse course:courses){
                class_span=class_span+span[course.getWeekday()];
            }
            myclass.setClass_span(class_span);
            myclass.setCourses(courses);
            myclass.setPlace(place);
            myclass.setTeacher(teacher);
        }
        return classes;
    }
    public ArrayList<BeanClass> getClasses() throws Exception {
        String sql="select class_id,class_name,class_state,class_price,teacher_id,place_id,num,class_url from class where status=1 order by `type` ";
        ArrayList<BeanClass> classes=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
            @Override
            public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanClass myclass=new BeanClass();
                    myclass.setClass_id(rs.getInt(1));
                    myclass.setClass_name(rs.getString(2));
                    myclass.setClass_state(rs.getBoolean(3));
                    myclass.setClass_price(rs.getDouble(4));
                    myclass.setTeacher_id(rs.getString(5));
                    myclass.setPlace_id(rs.getInt(6));
                    myclass.setNum(rs.getInt(7));
                    myclass.setClass_url(rs.getString(8));
                    classes.add(myclass);
                }
                return null;
            }});
        for(BeanClass myclass:classes){
            sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher where teacher_id=?";
            BeanTeacher teacher=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
                @Override
                public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanTeacher teacher=new BeanTeacher();
                        teacher.setTeacher_id(rs.getString(1));
                        teacher.setTeacher_name(rs.getString(2));
                        teacher.setTeacher_photo(rs.getString(3));
                        teacher.setTeacher_introduction(rs.getString(4));
                        return teacher;
                    }
                    return null;
                }},myclass.getTeacher_id());
            sql="select place_id,place_name,place_photo,place_introduction from place where place_id=?";
            BeanPlace place=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanPlace>() {
                @Override
                public BeanPlace extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanPlace place=new BeanPlace();
                        place.setPlace_id(rs.getInt(1));
                        place.setPlace_name(rs.getString(2));
                        place.setPlace_photo(rs.getString(3));
                        place.setPlace_introduction(rs.getString(4));
                        return place;
                    }
                    return null;
                }},myclass.getPlace_id());
            List<BeanCourse> courses = new ArrayList<BeanCourse>();
            sql="select course_id,course_state,course_evaluation,weekday,span from course where class_id=?";
            this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanCourse>() {
                @Override
                public BeanCourse extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    while(rs.next()) {
                        BeanCourse course=new BeanCourse();
                        course.setCourse_id(rs.getInt(1));
                        course.setCourse_state(rs.getBoolean(2));
                        course.setCourse_evaluation(rs.getString(3));
                        course.setWeekday(rs.getInt(4));
                        course.setSpan(rs.getInt(5));
                        courses.add(course);
                    }
                    return null;
                }},myclass.getClass_id());
            myclass.setCourses(courses);
            myclass.setPlace(place);
            myclass.setTeacher(teacher);
        }
        return classes;
    }
    public void classAdd(String class_name,String class_url,double class_price,int type,int num,String teacher_id,int place_id) throws Exception {
        String sql="insert into `class`(class_name,class_url,class_price,`type`,num,teacher_id,place_id,class_state,status) values(?,?,?,?,?,?,?,0,1)";
        this.jdbcTemplate.update(sql,new Object[] {class_name,class_url,class_price,type,num,teacher_id,place_id});
        int class_id=this.jdbcTemplate.queryForObject("select max(class_id) from `class`", Integer.class);
        for(int i=2;i<=6;i+=2){
            int span=(i==6)?2:4;
            sql="insert into course(class_id,weekday,span) values(?,?,?)";
            this.jdbcTemplate.update(sql,new Object[] {class_id,i,span});
        }
    }
    public void classUpdate(int class_id,String class_name,String class_url,int num,double class_price,String teacher_id,int place_id) throws Exception {
        String sql="update class set class_name=?,class_url=?,num=?,class_price=?,teacher_id=?,place_id=? where class_id=?";
        this.jdbcTemplate.update(sql,new Object[] {class_name,class_url,num,class_price,teacher_id,place_id,class_id});
    }
    public void classDelete(int class_id) throws Exception{
        String sql="update class set status=0 where class_id=?";
        this.jdbcTemplate.update(sql,new Object[] {class_id});
    }
}
